package com.pai4j.user.repository.dao;
import com.pai4j.user.repository.entity.FriendEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 *  好友列表DAO
 *
 * @author: CYM-pai
 * @date: 2025/10/23 20:20
 **/
@Repository
public interface IFriendDAO extends JpaSpecificationExecutor<FriendEntity>, JpaRepository<FriendEntity, Long> {

    FriendEntity findAllByUserIdAndFriendId(String userId, String friendId);

    Long countByFriendIdAndStatus(String friendId, Integer status);
    
    Long countByUserIdAndStatus(String userId, Integer status);

    List<FriendEntity> findAllByFriendIdAndStatusOrderByApplyDateDesc(String friendId, Integer status);

    List<FriendEntity> findAllByUserIdAndStatusOrderByApplyDateDesc(String userId, Integer status);

    @Query("select f from FriendEntity f inner join UserEntity u on u.userId = f.friendId " +
            "where f.userId = :userId and f.status = :status and u.userName like :userName order by f.applyDate desc")
    List<FriendEntity> findAllByUserIdAndUserNameLikeAndStatusOrderByApplyDateDesc(String userId, String userName, Integer status);

    // ==================== 数据分析相关查询 ====================
    
    /**
     * 统计指定时间段内新增的粉丝数量（某用户被关注的数量）
     * 
     * @param friendId 被关注者账号
     * @param status 状态（1=关注中）
     * @param startDate 开始时间
     * @param endDate 结束时间
     * @return 新增粉丝数量
     */
    @Query(nativeQuery = true,
            value = "SELECT COUNT(*) FROM pai4j_friend " +
                    "WHERE friend_id = :friendId " +
                    "AND status = :status " +
                    "AND handle_date >= :startDate " +
                    "AND handle_date < :endDate")
    Long countNewFollowersByDateRange(@Param("friendId") String friendId,
                                      @Param("status") Integer status,
                                      @Param("startDate") Date startDate,
                                      @Param("endDate") Date endDate);

    /**
     * 统计指定时间段内取关的数量
     * 
     * @param friendId 被关注者账号
     * @param status 状态（0=已取关）
     * @param startDate 开始时间
     * @param endDate 结束时间
     * @return 取关数量
     */
    @Query(nativeQuery = true,
            value = "SELECT COUNT(*) FROM pai4j_friend " +
                    "WHERE friend_id = :friendId " +
                    "AND status = :status " +
                    "AND handle_date >= :startDate " +
                    "AND handle_date < :endDate")
    Long countUnfollowsByDateRange(@Param("friendId") String friendId,
                                    @Param("status") Integer status,
                                    @Param("startDate") Date startDate,
                                    @Param("endDate") Date endDate);

    /**
     * 统计指定时间段内新增的关注数量（某用户关注他人的数量）
     * 
     * @param userId 用户账号
     * @param status 状态（1=关注中）
     * @param startDate 开始时间
     * @param endDate 结束时间
     * @return 新增关注数量
     */
    @Query(nativeQuery = true,
            value = "SELECT COUNT(*) FROM pai4j_friend " +
                    "WHERE user_id = :userId " +
                    "AND status = :status " +
                    "AND handle_date >= :startDate " +
                    "AND handle_date < :endDate")
    Long countNewFollowingByDateRange(@Param("userId") String userId,
                                       @Param("status") Integer status,
                                       @Param("startDate") Date startDate,
                                       @Param("endDate") Date endDate);

    /**
     * 按日期分组统计粉丝增长趋势
     * 返回格式：[{date: '2025-01-01', count: 10}, {date: '2025-01-02', count: 15}, ...]
     * 
     * @param friendId 被关注者账号
     * @param status 状态（1=关注中）
     * @param startDate 开始时间
     * @param endDate 结束时间
     * @return 按日期分组的统计结果
     */
    @Query(nativeQuery = true,
            value = "SELECT DATE(handle_date) as date, COUNT(*) as count " +
                    "FROM pai4j_friend " +
                    "WHERE friend_id = :friendId " +
                    "AND status = :status " +
                    "AND handle_date >= :startDate " +
                    "AND handle_date < :endDate " +
                    "GROUP BY DATE(handle_date) " +
                    "ORDER BY DATE(handle_date)")
    List<Object[]> getFollowersTrendByDay(@Param("friendId") String friendId,
                                           @Param("status") Integer status,
                                           @Param("startDate") Date startDate,
                                           @Param("endDate") Date endDate);

    /**
     * 按日期分组统计关注增长趋势
     * 
     * @param userId 用户账号
     * @param status 状态（1=关注中）
     * @param startDate 开始时间
     * @param endDate 结束时间
     * @return 按日期分组的统计结果
     */
    @Query(nativeQuery = true,
            value = "SELECT DATE(handle_date) as date, COUNT(*) as count " +
                    "FROM pai4j_friend " +
                    "WHERE user_id = :userId " +
                    "AND status = :status " +
                    "AND handle_date >= :startDate " +
                    "AND handle_date < :endDate " +
                    "GROUP BY DATE(handle_date) " +
                    "ORDER BY DATE(handle_date)")
    List<Object[]> getFollowingTrendByDay(@Param("userId") String userId,
                                           @Param("status") Integer status,
                                           @Param("startDate") Date startDate,
                                           @Param("endDate") Date endDate);

    /**
     * 获取最近N天每天的累计粉丝数
     * 通过子查询统计每天结束时的累计粉丝数
     * 
     * @param friendId 被关注者账号
     * @param status 状态（1=关注中）
     * @param days 天数
     * @return 累计粉丝数趋势
     */
    @Query(nativeQuery = true,
            value = "SELECT DATE(d.date) as date, " +
                    "COALESCE(SUM(CASE WHEN f.handle_date <= d.date AND f.status = :status THEN 1 ELSE 0 END), 0) as cumulative_count " +
                    "FROM (" +
                    "  SELECT DATE(DATE_SUB(CURDATE(), INTERVAL seq DAY)) as date " +
                    "  FROM (SELECT 0 as seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 " +
                    "        UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 " +
                    "        UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 " +
                    "        UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 " +
                    "        UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 " +
                    "        UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 " +
                    "        UNION ALL SELECT 30) as seq_table " +
                    "  WHERE seq < :days " +
                    ") as d " +
                    "LEFT JOIN pai4j_friend f ON f.friend_id = :friendId " +
                    "GROUP BY DATE(d.date) " +
                    "ORDER BY DATE(d.date)")
    List<Object[]> getCumulativeFollowersTrend(@Param("friendId") String friendId,
                                                @Param("status") Integer status,
                                                @Param("days") Integer days);
}
